package db;

/**
 * DbConnector.java creates access to BankDatabase 
 *  
 * @author XA9
 * 
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbConnector {

	private static DbConnector unique = null;
	/** database sqlURL */
	private static String sqlUrl = "jdbc:mysql://localhost:3306/AtmDatabase";
	/** database sqlUsername */
	private static String sqlUser = "root";
	/** database sqlPassword */
	private static String sqlPasswd = "";
	/** database sqlDriver */
	private static String sqlDriver = "com.mysql.jdbc.Driver";

	/**
	 * Initialize values of variables needed to work with the database
	 * 
	 * @param _sqlUrl
	 * @param _sqlPort
	 * @param _sqlUser
	 * @param _sqlPasswd
	 * @param db
	 * 
	 */
	public static void setDatabaseInfomation(String _sqlUrl, String _sqlPort,
			String _sqlUser, String _sqlPasswd, String db) {
		sqlUrl = "jdbc:mysql://" + _sqlUrl + ":" + _sqlPort + "/";
		sqlUser = _sqlUser;
		sqlPasswd = _sqlPasswd;

		if (db != null && db.length() > 0)
			sqlUrl += db;
	}

	/**
	 * Private constructor ensures that an instance of the class only can be
	 * given by calling the method getInstance()
	 * 
	 * 
	 */
	private DbConnector() {
		try {
			Class.forName(sqlDriver).newInstance();
		}

		catch (Exception ex) {
			System.out.println(ex);
		}
	}

	/**
	 * Method getInstance is used to access objects of DbConnector.java
	 * 
	 * @return unique object of DbConnector
	 */
	public static DbConnector getInstance() {
		if (unique == null)
			unique = new DbConnector();

		return unique;
	}

	/**
	 * Method doUpdate sends an update to BankDatabase
	 * 
	 * @param user
	 *            SQL statement
	 * 
	 */
	public void doUpdate(String update) throws SQLException {
		DatabaseConn db = new DatabaseConn();

		db.doUpdate(update);

	}

	/**
	 * Method doQuery sends an query to BankDatabase
	 * 
	 * @param user
	 *            SQL statement
	 * @return a ResultSet that contains the data produced by user SQL query
	 * 
	 */
	public ResultSet doQuery(String query) throws SQLException {
		DatabaseConn db = new DatabaseConn();

		return db.doQuery(query);
	}

	/**
	 * Test if connected to BankDatabase
	 * 
	 * @return "Succesfully connected" if valid. Else the return string will be
	 *         an error
	 * 
	 */
	public String testConnection() {
		DatabaseConn db = new DatabaseConn();
		String result = db.testConnection();
		return result;
	}

	private class DatabaseConn {

		private Statement stmt;
		private ResultSet rs;
		private Connection conn = null;

		public String testConnection() {

			String result = "Succesfully connected";

			try {
				conn = DriverManager.getConnection(sqlUrl, sqlUser, sqlPasswd);
				if (conn.isValid(5000))
					return result;
			} catch (SQLException e) {

				result = e.getMessage();

			}

			return result;
		}

		/**
		 * Method doQuery sends and query to BankDatabase
		 * 
		 * @param user
		 *            SQL statement
		 * @return a ResultSet that contains the data produced by user SQL query
		 */
		public ResultSet doQuery(String query) throws SQLException {

			conn = DriverManager.getConnection(sqlUrl, sqlUser, sqlPasswd);
			try {
				stmt = conn.createStatement();
				rs = stmt.executeQuery(query);
			}

			catch (SQLException ex) {
				System.out.println(ex);
				throw ex;
			}

			return rs;
		}

		/**
		 * Method doUpdate sends an update to BankDatabase
		 * 
		 * @param user
		 *            SQL statement
		 * @return either (1) the row count for SQL Data Manipulation Language
		 *         statements or (2) 0 for SQL statements that return nothing
		 * 
		 */
		public int doUpdate(String update) throws SQLException {
			if (conn == null)
				conn = DriverManager.getConnection(sqlUrl, sqlUser, sqlPasswd);

			int temp = 0;

			try {
				stmt = conn.createStatement();
				temp = stmt.executeUpdate(update);
			}

			catch (SQLException ex) {
				ex.printStackTrace();
				if (ex.getErrorCode() == 1062)
					throw new SQLException("Duplicate entry");
				else
					throw ex;
			}

			finally {
				if (stmt != null) {
					try {
						stmt.close();
					}

					catch (SQLException sqlEx) { // ignore
						stmt = null;
					}
				}
			}

			return temp;
		}

	}
}
